package com.itheima.mapper;

import com.itheima.entiry.ValueName;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;
@Mapper
public interface StaticsMapper {
    @Select("SELECT category.name,SUM(order_detail.amount) as `value` " +
            "from category,dish,order_detail  where dish.category_id=category.id " +
            "and dish.id = order_detail.dish_id GROUP BY category.name")
    List<ValueName> getSum();

    @Select("SELECT category.`name`,SUM(order_detail.amount) as ``value" +
            "from category,setmeal,order_detail  where setmeal.category_id=category.id " +
            "and setmeal.id = order_detail.setmeal_id GROUP BY category.`name`")
    List<ValueName> getSum2();

    @Select("SELECT category.name,COUNT(*) as `value` " +
            "from category,dish,order_detail  where dish.category_id=category.id " +
            "and dish.id = order_detail.dish_id GROUP BY category.name")
    List<ValueName> getCount();

    @Select("SELECT category.`name`,COUNT(*) as ``value" +
            "from category,setmeal,order_detail  where setmeal.category_id=category.id " +
            "and setmeal.id = order_detail.setmeal_id GROUP BY category.`name`")
    List<ValueName> getCount2();

    @Select("select DATE_FORMAT(orders.order_time,'%Y-%m'), sum(order_detail.amount) sum  " +
            "from orders,order_detail group by month(orders.order_time) order by orders.order_time")
    List<ValueName> cashPerMonth();


}
